using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VMGBST
    {
        public DataRow Convert2MGBST(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryMGBST(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["MGMANDANT"] = " ".PadRight(2 ); 
         dwReturn["MGSA"] = " ".PadRight(2 ); 
         dwReturn["MGMAT"] = " ".PadRight(14 ); 
         dwReturn["MGVAR"] = " ".PadRight(3 ); 
         dwReturn["MGLO"] = " ".PadRight(2 ); 
         dwReturn["MGMJJ"] = " ".PadRight(0 ); 
         dwReturn["MGMM"] = " ".PadRight(0 ); 
         dwReturn["MGAKTKZ"] = " ".PadRight(1 ); 
         dwReturn["MGME01"] = " ".PadRight(0 ); 
         dwReturn["MGME10"] = " ".PadRight(0 ); 
         dwReturn["MGME11"] = " ".PadRight(0 ); 
         dwReturn["MGME12"] = " ".PadRight(0 ); 
         dwReturn["MGME13"] = " ".PadRight(0 ); 
         dwReturn["MGME19"] = " ".PadRight(0 ); 
         dwReturn["MGME20"] = " ".PadRight(0 ); 
         dwReturn["MGME21"] = " ".PadRight(0 ); 
         dwReturn["MGME31"] = " ".PadRight(0 ); 
         dwReturn["MGME32"] = " ".PadRight(0 ); 
         dwReturn["MGME33"] = " ".PadRight(0 ); 
         dwReturn["MGME34"] = " ".PadRight(0 ); 
         dwReturn["MGME35"] = " ".PadRight(0 ); 
         dwReturn["MGME36"] = " ".PadRight(0 ); 
         dwReturn["MGME42"] = " ".PadRight(0 ); 
         dwReturn["MGME43"] = " ".PadRight(0 ); 
         dwReturn["MGME46"] = " ".PadRight(0 ); 
         dwReturn["MGME47"] = " ".PadRight(0 ); 
         dwReturn["MGME52"] = " ".PadRight(0 ); 
         dwReturn["MGME53"] = " ".PadRight(0 ); 
         dwReturn["MGME55"] = " ".PadRight(0 ); 
         dwReturn["MGME56"] = " ".PadRight(0 ); 
         dwReturn["MGMEIV"] = " ".PadRight(0 ); 
         dwReturn["MGME76"] = " ".PadRight(0 ); 
         dwReturn["MGME79"] = " ".PadRight(0 ); 
         dwReturn["MGME80"] = " ".PadRight(0 ); 
         dwReturn["MGME88"] = " ".PadRight(0 ); 
         dwReturn["MGME89"] = " ".PadRight(0 ); 
         dwReturn["MGME90"] = " ".PadRight(0 ); 
         dwReturn["MGME91"] = " ".PadRight(0 ); 
         dwReturn["MGME24"] = " ".PadRight(0 ); 
         dwReturn["MGBSTKMA"] = " ".PadRight(0 ); 
         dwReturn["MGBSTKME"] = " ".PadRight(0 ); 
         dwReturn["MGBSTVMA"] = " ".PadRight(0 ); 
         dwReturn["MGBSTVME"] = " ".PadRight(0 ); 
         dwReturn["MGBSTA"] = " ".PadRight(0 ); 
         dwReturn["MGBSTAN"] = " ".PadRight(0 ); 
         dwReturn["MGBSTMA"] = " ".PadRight(0 ); 
         dwReturn["MGBSTME"] = " ".PadRight(0 ); 
         dwReturn["MGINDAT"] = " ".PadRight(0 ); 
         dwReturn["MGINBST"] = " ".PadRight(0 ); 
         dwReturn["MGINALT"] = " ".PadRight(0 ); 
         dwReturn["MGEKPRMA"] = " ".PadRight(0 ); 
         dwReturn["MGEKPRME"] = " ".PadRight(0 ); 
         dwReturn["MGEKPRZU"] = " ".PadRight(0 ); 
         dwReturn["MGVRPR"] = " ".PadRight(0 ); 
         dwReturn["MGDLABST"] = " ".PadRight(0 ); 
         dwReturn["MGDLATAG"] = " ".PadRight(2 ); 
         dwReturn["MGMINBST"] = " ".PadRight(0 ); 
         dwReturn["MGMAXBST"] = " ".PadRight(0 ); 
         dwReturn["MGFERTKZ"] = " ".PadRight(1 ); 
         dwReturn["MGUEBER"] = " ".PadRight(1 ); 
         dwReturn["MGAENDZL"] = " ".PadRight(0 ); 
         dwReturn["MGAENDBEN"] = " ".PadRight(8 ); 
         dwReturn["MGAENDDAT"] = " ".PadRight(0 ); 
         dwReturn["MGFILLER"] = " ".PadRight(8 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapMGBST(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveMGBST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryMGBST(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapMGBST(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertMGBST(dwLocal, ITXDBVERSION);
            }
            UpdateMGBST(dwLocal);
        }
        public void InsertMGBST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO MGBST(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryMGBST(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM MGBST WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM MGBST  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "MGBST";
            Return = Result;
            return Return;
        }
        public void DeleteMGBST(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM MGBST   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateMGBST(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE MGBST SET "
                    +"MGMANDANT= :MGMANDANT,"
                    +"MGSA= :MGSA,"
                    +"MGMAT= :MGMAT,"
                    +"MGVAR= :MGVAR,"
                    +"MGLO= :MGLO,"
                    +"MGMJJ= :MGMJJ,"
                    +"MGMM= :MGMM,"
                    +"MGAKTKZ= :MGAKTKZ,"
                    +"MGME01= :MGME01,"
                    +"MGME10= :MGME10,"
                    +"MGME11= :MGME11,"
                    +"MGME12= :MGME12,"
                    +"MGME13= :MGME13,"
                    +"MGME19= :MGME19,"
                    +"MGME20= :MGME20,"
                    +"MGME21= :MGME21,"
                    +"MGME31= :MGME31,"
                    +"MGME32= :MGME32,"
                    +"MGME33= :MGME33,"
                    +"MGME34= :MGME34,"
                    +"MGME35= :MGME35,"
                    +"MGME36= :MGME36,"
                    +"MGME42= :MGME42,"
                    +"MGME43= :MGME43,"
                    +"MGME46= :MGME46,"
                    +"MGME47= :MGME47,"
                    +"MGME52= :MGME52,"
                    +"MGME53= :MGME53,"
                    +"MGME55= :MGME55,"
                    +"MGME56= :MGME56,"
                    +"MGMEIV= :MGMEIV,"
                    +"MGME76= :MGME76,"
                    +"MGME79= :MGME79,"
                    +"MGME80= :MGME80,"
                    +"MGME88= :MGME88,"
                    +"MGME89= :MGME89,"
                    +"MGME90= :MGME90,"
                    +"MGME91= :MGME91,"
                    +"MGME24= :MGME24,"
                    +"MGBSTKMA= :MGBSTKMA,"
                    +"MGBSTKME= :MGBSTKME,"
                    +"MGBSTVMA= :MGBSTVMA,"
                    +"MGBSTVME= :MGBSTVME,"
                    +"MGBSTA= :MGBSTA,"
                    +"MGBSTAN= :MGBSTAN,"
                    +"MGBSTMA= :MGBSTMA,"
                    +"MGBSTME= :MGBSTME,"
                    +"MGINDAT= :MGINDAT,"
                    +"MGINBST= :MGINBST,"
                    +"MGINALT= :MGINALT,"
                    +"MGEKPRMA= :MGEKPRMA,"
                    +"MGEKPRME= :MGEKPRME,"
                    +"MGEKPRZU= :MGEKPRZU,"
                    +"MGVRPR= :MGVRPR,"
                    +"MGDLABST= :MGDLABST,"
                    +"MGDLATAG= :MGDLATAG,"
                    +"MGMINBST= :MGMINBST,"
                    +"MGMAXBST= :MGMAXBST,"
                    +"MGFERTKZ= :MGFERTKZ,"
                    +"MGUEBER= :MGUEBER,"
                    +"MGAENDZL= :MGAENDZL,"
                    +"MGAENDBEN= :MGAENDBEN,"
                    +"MGAENDDAT= :MGAENDDAT,"
                    +"MGFILLER= :MGFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("MGMANDANT", OracleType.NVarChar).Value = dwData["MGMANDANT"].ToString(); 
          or1.Parameters.Add("MGSA", OracleType.NVarChar).Value = dwData["MGSA"].ToString(); 
          or1.Parameters.Add("MGMAT", OracleType.NVarChar).Value = dwData["MGMAT"].ToString(); 
          or1.Parameters.Add("MGVAR", OracleType.NVarChar).Value = dwData["MGVAR"].ToString(); 
          or1.Parameters.Add("MGLO", OracleType.NVarChar).Value = dwData["MGLO"].ToString(); 
          or1.Parameters.Add("MGMJJ", OracleType.NVarChar).Value = dwData["MGMJJ"].ToString(); 
          or1.Parameters.Add("MGMM", OracleType.NVarChar).Value = dwData["MGMM"].ToString(); 
          or1.Parameters.Add("MGAKTKZ", OracleType.NVarChar).Value = dwData["MGAKTKZ"].ToString(); 
          or1.Parameters.Add("MGME01", OracleType.NVarChar).Value = dwData["MGME01"].ToString(); 
          or1.Parameters.Add("MGME10", OracleType.NVarChar).Value = dwData["MGME10"].ToString(); 
          or1.Parameters.Add("MGME11", OracleType.NVarChar).Value = dwData["MGME11"].ToString(); 
          or1.Parameters.Add("MGME12", OracleType.NVarChar).Value = dwData["MGME12"].ToString(); 
          or1.Parameters.Add("MGME13", OracleType.NVarChar).Value = dwData["MGME13"].ToString(); 
          or1.Parameters.Add("MGME19", OracleType.NVarChar).Value = dwData["MGME19"].ToString(); 
          or1.Parameters.Add("MGME20", OracleType.NVarChar).Value = dwData["MGME20"].ToString(); 
          or1.Parameters.Add("MGME21", OracleType.NVarChar).Value = dwData["MGME21"].ToString(); 
          or1.Parameters.Add("MGME31", OracleType.NVarChar).Value = dwData["MGME31"].ToString(); 
          or1.Parameters.Add("MGME32", OracleType.NVarChar).Value = dwData["MGME32"].ToString(); 
          or1.Parameters.Add("MGME33", OracleType.NVarChar).Value = dwData["MGME33"].ToString(); 
          or1.Parameters.Add("MGME34", OracleType.NVarChar).Value = dwData["MGME34"].ToString(); 
          or1.Parameters.Add("MGME35", OracleType.NVarChar).Value = dwData["MGME35"].ToString(); 
          or1.Parameters.Add("MGME36", OracleType.NVarChar).Value = dwData["MGME36"].ToString(); 
          or1.Parameters.Add("MGME42", OracleType.NVarChar).Value = dwData["MGME42"].ToString(); 
          or1.Parameters.Add("MGME43", OracleType.NVarChar).Value = dwData["MGME43"].ToString(); 
          or1.Parameters.Add("MGME46", OracleType.NVarChar).Value = dwData["MGME46"].ToString(); 
          or1.Parameters.Add("MGME47", OracleType.NVarChar).Value = dwData["MGME47"].ToString(); 
          or1.Parameters.Add("MGME52", OracleType.NVarChar).Value = dwData["MGME52"].ToString(); 
          or1.Parameters.Add("MGME53", OracleType.NVarChar).Value = dwData["MGME53"].ToString(); 
          or1.Parameters.Add("MGME55", OracleType.NVarChar).Value = dwData["MGME55"].ToString(); 
          or1.Parameters.Add("MGME56", OracleType.NVarChar).Value = dwData["MGME56"].ToString(); 
          or1.Parameters.Add("MGMEIV", OracleType.NVarChar).Value = dwData["MGMEIV"].ToString(); 
          or1.Parameters.Add("MGME76", OracleType.NVarChar).Value = dwData["MGME76"].ToString(); 
          or1.Parameters.Add("MGME79", OracleType.NVarChar).Value = dwData["MGME79"].ToString(); 
          or1.Parameters.Add("MGME80", OracleType.NVarChar).Value = dwData["MGME80"].ToString(); 
          or1.Parameters.Add("MGME88", OracleType.NVarChar).Value = dwData["MGME88"].ToString(); 
          or1.Parameters.Add("MGME89", OracleType.NVarChar).Value = dwData["MGME89"].ToString(); 
          or1.Parameters.Add("MGME90", OracleType.NVarChar).Value = dwData["MGME90"].ToString(); 
          or1.Parameters.Add("MGME91", OracleType.NVarChar).Value = dwData["MGME91"].ToString(); 
          or1.Parameters.Add("MGME24", OracleType.NVarChar).Value = dwData["MGME24"].ToString(); 
          or1.Parameters.Add("MGBSTKMA", OracleType.NVarChar).Value = dwData["MGBSTKMA"].ToString(); 
          or1.Parameters.Add("MGBSTKME", OracleType.NVarChar).Value = dwData["MGBSTKME"].ToString(); 
          or1.Parameters.Add("MGBSTVMA", OracleType.NVarChar).Value = dwData["MGBSTVMA"].ToString(); 
          or1.Parameters.Add("MGBSTVME", OracleType.NVarChar).Value = dwData["MGBSTVME"].ToString(); 
          or1.Parameters.Add("MGBSTA", OracleType.NVarChar).Value = dwData["MGBSTA"].ToString(); 
          or1.Parameters.Add("MGBSTAN", OracleType.NVarChar).Value = dwData["MGBSTAN"].ToString(); 
          or1.Parameters.Add("MGBSTMA", OracleType.NVarChar).Value = dwData["MGBSTMA"].ToString(); 
          or1.Parameters.Add("MGBSTME", OracleType.NVarChar).Value = dwData["MGBSTME"].ToString(); 
          or1.Parameters.Add("MGINDAT", OracleType.NVarChar).Value = dwData["MGINDAT"].ToString(); 
          or1.Parameters.Add("MGINBST", OracleType.NVarChar).Value = dwData["MGINBST"].ToString(); 
          or1.Parameters.Add("MGINALT", OracleType.NVarChar).Value = dwData["MGINALT"].ToString(); 
          or1.Parameters.Add("MGEKPRMA", OracleType.NVarChar).Value = dwData["MGEKPRMA"].ToString(); 
          or1.Parameters.Add("MGEKPRME", OracleType.NVarChar).Value = dwData["MGEKPRME"].ToString(); 
          or1.Parameters.Add("MGEKPRZU", OracleType.NVarChar).Value = dwData["MGEKPRZU"].ToString(); 
          or1.Parameters.Add("MGVRPR", OracleType.NVarChar).Value = dwData["MGVRPR"].ToString(); 
          or1.Parameters.Add("MGDLABST", OracleType.NVarChar).Value = dwData["MGDLABST"].ToString(); 
          or1.Parameters.Add("MGDLATAG", OracleType.NVarChar).Value = dwData["MGDLATAG"].ToString(); 
          or1.Parameters.Add("MGMINBST", OracleType.NVarChar).Value = dwData["MGMINBST"].ToString(); 
          or1.Parameters.Add("MGMAXBST", OracleType.NVarChar).Value = dwData["MGMAXBST"].ToString(); 
          or1.Parameters.Add("MGFERTKZ", OracleType.NVarChar).Value = dwData["MGFERTKZ"].ToString(); 
          or1.Parameters.Add("MGUEBER", OracleType.NVarChar).Value = dwData["MGUEBER"].ToString(); 
          or1.Parameters.Add("MGAENDZL", OracleType.NVarChar).Value = dwData["MGAENDZL"].ToString(); 
          or1.Parameters.Add("MGAENDBEN", OracleType.NVarChar).Value = dwData["MGAENDBEN"].ToString(); 
          or1.Parameters.Add("MGAENDDAT", OracleType.NVarChar).Value = dwData["MGAENDDAT"].ToString(); 
          or1.Parameters.Add("MGFILLER", OracleType.NVarChar).Value = dwData["MGFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
